package com.mva.User_DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.mva.common.DateUtils;
import com.mva.database.DB_Pipe;

public class UserDB implements UserDAO
  {
   private Connection con = null;
	   
   public UserDB()
	 {
	 }

   public boolean existsUser (String User)
	 {
	  String tmpUser = User.trim();
	         tmpUser = tmpUser.toUpperCase();
	  try
		{
	     String exists = "SELECT User "      +
				           "FROM user "      +
				          "WHERE User  = ?";
                           con = DB_Pipe.DB_Connect();
	     PreparedStatement ps  = con.prepareStatement(exists);
		 ps.setString(1, tmpUser);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean getUsers (ArrayList<User> users)
	 {
	  try
		{
		 String qry = "SELECT User, "      +
		                     "Nm_Pfx, "    +
		                     "Nm_Fst, "    +
		                     "Nm_Mid, "    +
		                     "Nm_Lst, "    +
		                     "Nm_Sfx, "    +
		                     "Addr, "      +
		                     "Phn, "       +
		                     "Phn2, "      +
		                     "Email, "     +
		                     "Email2 "     +
			 	        "FROM user "       +
				       "ORDER BY User ASC";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(qry);
		 ResultSet         rs  = ps.executeQuery();
		 while(rs.next())
		   {
		    String  User   = rs.getString("User");
		    String  Nm_Pfx = rs.getString("Nm_Pfx");
		    String  Nm_Fst = rs.getString("Nm_Fst");
		    String  Nm_Mid = rs.getString("Nm_Mid");
		    String  Nm_Lst = rs.getString("Nm_Lst");
		    String  Nm_Sfx = rs.getString("Nm_Sfx");
		    int     Addr   = rs.getInt("Addr");
		    String  Phn1   = rs.getString("Phn");
		    String  Phn2   = rs.getString("Phn2");
		    String  Email  = rs.getString("Email");
		    String  Email2 = rs.getString("Email2");
		    User    u      = new User(User, Nm_Pfx, Nm_Fst, Nm_Mid, Nm_Lst, Nm_Sfx, Addr, Phn1, Phn2, Email, Email2);
		    users.add(u);
		   }
		 rs.close();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return false;
		}
	 }

   public User getUser (String User)
	 {
	  try
		{
		 String sel = "SELECT User, "   +
		                     "Nm_Pfx, " +
		                     "Nm_Fst, " +
		                     "Nm_Mid, " +
		                     "Nm_Lst, " +
		                     "Nm_Sfx, " +
		                     "Addr, "   +
		                     "Phn, "    +
				             "Phn2, "   +
				             "Email, "  +
				             "Email2 "  +
				        "FROM user "    +
				       "WHERE User = ?";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(sel);
		 ps.setString(1, User);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    String Nm_Pfx = rs.getString("Nm_Pfx");
		    String Nm_Fst = rs.getString("Nm_Fst");
		    String Nm_Mid = rs.getString("Nm_Mid");
		    String Nm_Lst = rs.getString("Nm_Lst");
		    String Nm_Sfx = rs.getString("Nm_Sfx");
		    int    Addr   = rs.getInt("Addr");
		    String Phn1   = rs.getString("Phn");
		    String Phn2   = rs.getString("Phn2");
		    String Email  = rs.getString("Email");
		    String Email2 = rs.getString("Email2");
		    User u        = new User(User, Nm_Pfx, Nm_Fst, Nm_Mid, Nm_Lst, Nm_Sfx, Addr, Phn1, Phn2, Email, Email2);
		    rs.close();
		    ps.close();
		    DB_Pipe.DB_Disconnect(con);
		    return u;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	 }

   public boolean addUser (User u)
	 {
	  try
		{
		 String ins = "INSERT "                   +
		                "INTO user (User, "       +
		                            "Nm_Pfx, "    +
		                            "Nm_Fst, "    +
		                            "Nm_Mid, "    +
		                            "Nm_Lst, "    +
		                            "Nm_Sfx, "    +
		                            "Addr, "      +
		                            "Phn, "       +
		                            "Phn2, "      +
		                            "Email, "     +
		                            "Email2, "    +
		                            "TMS_Updt, "  +
		                            "User_Updt, " +
		                            "TMS_Crtd, "  +
		                            "User_Crtd) " +
				      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
         con                   = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(ins);
	     Timestamp         ts  = DateUtils.getTmstmp();
		 ps.setString(1, u.getUser());
		 ps.setString(2, u.getNmPfx());
		 ps.setString(3, u.getNmFst());
		 ps.setString(4, u.getNmMid());
		 ps.setString(5, u.getNmLst());
		 ps.setString(6, u.getNmSfx());
		 ps.setInt(7, u.getAddr());
		 ps.setString(8, u.getPhn1());
		 ps.setString(9, u.getPhn2());
		 ps.setString(10, u.getEmail());
		 ps.setString(11, u.getEmail2());
		 ps.setTimestamp(12, ts);
		 ps.setString(13, "USER0001");
		 ps.setTimestamp(14, ts);
		 ps.setString(15, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean deleteUser (User u)
	 {
	  try
		{
		 String del = "DELETE "         +
		                "FROM user "    +
				       "WHERE User = ?";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(del);
		 ps.setString(1, u.getUser());
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean updateUser (User u)
	 {
	  try
		{
		 String upd = "UPDATE user "           +
		                 "SET Nm_Pfx    = ?, " +
		                     "Nm_Fst    = ?, " +
		                     "Nm_Mid    = ?, " +
		                     "Nm_Lst    = ?, " +
		                     "Nm_Sfx    = ?, " +
		                     "Addr      = ?, " +
		                     "Phn       = ?, " +
		                     "Phn2      = ?, " +
		                     "Email     = ?, " +
		                     "Email2    = ?, " +
		                     "TMS_Updt  = ?, " +
					         "User_Updt = ? "  +
				       "WHERE User = ?";
         con                   = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(upd);
	     Timestamp         ts  = DateUtils.getTmstmp();
		 ps.setString(1, u.getNmPfx());
		 ps.setString(2, u.getNmFst());
		 ps.setString(3, u.getNmMid());
		 ps.setString(4, u.getNmLst());
		 ps.setString(5, u.getNmSfx());
		 ps.setInt(6, u.getAddr());
		 ps.setString(7, u.getPhn1());
		 ps.setString(8, u.getPhn2());
		 ps.setString(9, u.getEmail());
		 ps.setString(10, u.getEmail2());
		 ps.setTimestamp(11, ts);
		 ps.setString(12, "USER8888");
		 ps.setString(13, u.getUser());
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	 }
  }